package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.Biologist;
import model.Genus;
import model.Specie;
import interfaces.ISpecies;

public class CSpecies implements ISpecies {
  private CConnection db;
  
  public CSpecies(CConnection db) {
	this.db = db;
  }

  @Override
  public boolean create(Specie specie) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	String sql = "insert into species (usual_name, scientific_name, observations, GenusName, Biologist) values ('"+specie.getUsualName()+"', '"+specie.getScientificName()+"',  '"+specie.getObservations()+"', '"+specie.getGenus().getName()+"', '" + specie.getBiologist().getPass_number() + "')";
	System.out.println(sql);
	int i = s.executeUpdate(sql);
	return i > 0;
  }
  
  @Override
  public boolean update(Specie specie) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	int i = s.executeUpdate("UPDATE species SET usual_name='"+specie.getUsualName()+"', observations='"+specie.getObservations()+"', GenusName='"+specie.getGenus().getName()+"', Biologist='" + specie.getBiologist().getPass_number() +  "' WHERE scientific_name='"+specie.getScientificName()+"'");
	return i > 0;
  }

  @Override
  public boolean delete(Specie specie) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	int i = s.executeUpdate("delete from species where scientific_name='"+specie.getScientificName()+"'");
	return i > 0;
  }

  @Override
  public ArrayList<Specie> retrieveByUsualName(String name) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	ResultSet res = s.executeQuery("select * from species where usual_name='" + name + "'");
				
	ArrayList<Specie> species = new ArrayList<Specie>();
			
	while(res.next()) {
	  Specie specie = new Specie();
	  specie.setUsualName(res.getString("usual_name"));
	  specie.setScientificName(res.getString("scientific_name"));
	  specie.setObservations(res.getString("Observations"));
	  Genus genus = new Genus();
	  genus.setName(res.getString("genusName"));
	  specie.setGenus(genus);
	  Biologist bio = new Biologist();
	  bio.setPass_number(res.getString("Biologist"));
	  species.add(specie);
	}
			
	return species;
  }

  @Override
  public ArrayList<Specie> retrieveByScientificName(String scientific) throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	ResultSet res = s.executeQuery("select * from species where scientific_name='" + scientific + "'");
		
	ArrayList<Specie> species = new ArrayList<Specie>();
	
	while(res.next()) {
	  Specie specie = new Specie();
	  specie.setUsualName(res.getString("usual_name"));
	  specie.setScientificName(res.getString("scientific_name"));
	  specie.setObservations(res.getString("Observations"));
	  Genus genus = new Genus();
	  genus.setName(res.getString("genusName"));
	  specie.setGenus(genus);
	  species.add(specie);
	}
		
	return species;
  }
	
  @Override
  public ArrayList<Specie> retrieveAll() throws SQLException, ClassNotFoundException {
	Statement s = db.getStatement();
	ResultSet res = s.executeQuery("select * from species");
		
	ArrayList<Specie> species = new ArrayList<Specie>();
	
	while(res.next()) {
	  Specie specie = new Specie();
	  specie.setUsualName(res.getString("usual_name"));
	  specie.setScientificName(res.getString("scientific_name"));
	  specie.setObservations(res.getString("Observations"));
	  Genus genus = new Genus();
	  genus.setName(res.getString("genusName"));
	  specie.setGenus(genus);
	  Biologist biologist = new Biologist();
	  biologist.setPass_number(res.getString("Biologist"));
	  specie.setBiologist(biologist);
	  species.add(specie);
	}
		
	return species;
  }
  
  public ArrayList<Object[]> countGroupingByGenus() throws SQLException, ClassNotFoundException {
		Statement s = db.getStatement();
		ResultSet res = s.executeQuery("select S.GENUSNAME, Count(*) as count from species S group by S.GENUSNAME order by count DESC");
			
		ArrayList<Object[]> species = new ArrayList<Object[]>();
		
		while(res.next()) 
		{
			Object[] obj = new Object[2];
			obj[0] = res.getString("genusname");
			obj[1] = res.getInt("count");
			species.add(obj);
		}
			
		return species;
	  }
  
  public ArrayList<Object[]> countGroupingByFamilies() throws SQLException, ClassNotFoundException {
		Statement s = db.getStatement();
		ResultSet res = s.executeQuery("select G.FAMILYNAME, Count(*) as count from species S join genus G on S.genusname = G.name group by G.FamilyName order by count DESC");
			
		ArrayList<Object[]> species = new ArrayList<Object[]>();
		
		while(res.next()) 
		{
			Object[] obj = new Object[2];
			obj[0] = res.getString("familyname");
			obj[1] = res.getInt("count");
			species.add(obj);
		}
			
		return species;
	  }
}
